package cn.jdy.plugins.service;

import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.log.BaseLog;
import cn.jdy.plugins.entity.Search;

/**
 * 搜索操作实现
 * 
 * @author songql
 * 
 */
@Service
public class SearchService extends BaseDao<Search> {
  /**
   * web
   * 搜索模糊查询
   * @return
   */
  public List<Map> queryByLike(Search search, String keywords) {
    try {
      String sql = "select a.id,a.title,a.summary,a.author,a.columnId,b.value0,1 type,a.createTime from News a,ConfValue b where a.columnId=b.id   and a.status='1' "
          + "and ( a.title like concat('%',?,'%') )"
          + " UNION "
          + "select e.id,e.title,e.summary,e.author,e.columnId,f.value0,2 type,e.createTime from product e,ConfValue f where e.columnId=f.id   and e.status='1' "
          + "and ( e.title like concat('%',?,'%')  )"
          + " UNION "
          + "select m.id,m.title,'' summary,'' author,m.columnId,n.value0,3 type,m.createTime from helpinfo m,ConfValue n where m.columnId=n.id   and m.status='1' "
          + "and (m.title like concat('%',?,'%')  ) order by createTime desc "
              + " limit "+search.getPagestart()+","+search.getPagesize();
      SQLQuery sqlQuery = em().createSQLQuery(sql);
      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
      return sqlQuery.list();
    } catch (Exception e) {
      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
    }
    return null;
  }
	
	/**
	 * web 搜索查询总行数
	 * @param keywords
	 * @return
	 */
	public String queryCount(String keywords) {
		String count="0";
		try {
			String sql = "select count(*) from ("
					+ "select a.id,a.title,a.summary,a.author,a.columnId,b.value0,1 type,a.createTime from News a,ConfValue b where a.columnId=b.id   and a.status='1' "
					+ "and ( a.title like concat('%',?,'%'))"
					+ " UNION "
					+ "select e.id,e.title,e.summary,e.author,e.columnId,f.value0,2 type,e.createTime from product e,ConfValue f where e.columnId=f.id   and e.status='1' "
					+ "and ( e.title like concat('%',?,'%'))"
					+ " UNION "
					+ "select m.id,m.title,'' summary,'' author,m.columnId,n.value0,3 type,m.createTime from helpinfo m,ConfValue n where m.columnId=n.id   and m.status='1' "
					+ "and (m.title like concat('%',?,'%')) order by createTime desc ) z";
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			List list = sqlQuery.list();
			count=list.get(0).toString();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "搜索查询总行数异常！", e);
		}
		return count;
	}
	
	
	/**
	 * 新闻，帮助，商品，商家联合模糊搜索查询
	 * @return
	 */
	public List<Map> queryByLikeNHPM(Search search, String keywords) {
		if(keywords=="")
			keywords=null;
		try {
			String sql = "select a.id,a.title,a.summary,1 type,a.falseCreateTime,a.imgUrl,'' costprice,'' currentprice  from News a where a.status='1' "
					+ "and ( a.title like concat('%',?,'%'))"
					+ " UNION "
					+ "select e.id,e.title,e.summary,2 type,e.createTime,e.imgUrl,e.shopprice costprice,e.price currentprice from productx e where e.status='0' "
					+ "and ( e.title like concat('%',?,'%'))"
					+ " UNION "
					+ "select g.id,g.name title,g.brief summary,4 type,g.createTime createTime,g.logo imgUrl,'' costprice,'' currentprice from mall g where g.status='1' "
					+ "and ( g.name like concat('%',?,'%') or g.brief like concat('%',?,'%')  or g.introduce like concat('%',?,'%'))"
					+ " UNION "
					+ "select m.id,m.title,'' summary,3 type,m.createTime,m.imgUrl,'' costprice,'' currentprice from helpinfo m where m.status='1' "
					+ "and (m.title like concat('%',?,'%')) order by createTime desc "
			        + " limit "+search.getPagestart()+","+search.getPagesize();
			
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			return sqlQuery.list();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "新闻，帮助，商品，商家联合模糊搜索查询异常！", e);
		}
		return null;
	}
	
	/**
	 * 新闻，帮助，商品，商家联合模糊搜索查询总行数
	 * @param keywords
	 * @return
	 */
	public int queryCountNHPM(String keywords) {
		int count = 0;
		try {
			String sql = "select count(*) from ("
					+ "select a.id,a.title,a.summary,1 type,a.createTime,a.imgUrl,'' costprice,'' currentprice  from News a where a.status='1' "
					+ "and ( a.title like '%"+keywords+"%'  or a.keyword like '%"+keywords+"%'  or a.summary like '%"+keywords+"%'  or a.context like '%"+keywords+"%')"
					+ " UNION "
					+ "select e.id,e.title,e.summary,2 type,e.createTime,e.imgUrl,e.shopprice costprice,e.price currentprice from productx e where e.status='0' "
					+ "and ( e.title like '%"+keywords+"%'  or e.keyword like '%"+keywords+"%'  or e.summary like '%"+keywords+"%'  or e.context like '%"+keywords+"%')"
					+ " UNION "
					+ "select g.id,g.name title,g.brief summary,4 type,g.createTime createTime,g.logo imgUrl,'' costprice,'' currentprice from mall g where g.status='1' "
					+ "and ( g.name like '%"+keywords+"%' or g.brief like '%"+keywords+"%'  or g.introduce like '%"+keywords+"%')"
					+ " UNION "
					+ "select m.id,m.title,'' summary,3 type,m.createTime,m.imgUrl,'' costprice,'' currentprice from helpinfo m where m.status='1' "
					+ "and (m.title like '%"+keywords+"%'  or m.keyword like '%"+keywords+"%'  or m.context like '%"+keywords+"%') order by createTime desc) z";
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			List list = sqlQuery.list();
			if(!(list == null || list.size() == 0)){
				//将bigInteger转换成Integer
				Object obj = list.get(0);
				count = Integer.parseInt(obj.toString());
			}
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "新闻，帮助，商品，商家联合模糊搜索查询总行数异常！", e);
		}
		return count;
	}
	
	/**
	 * 新闻，帮助联合模糊搜索查询
	 * 
	 * @return
	 */
	public List<Map> queryByLikeNH(Search search, String keywords) {
		try {
			String sql = "select a.id,a.title,a.summary,a.author,a.columnId,b.value0,1 type,a.createTime from News a,ConfValue b where a.columnId=b.id   and a.status='1' "
					+ "and (a.title like '%"+keywords+"%'  or a.keyword like '%"+keywords+"%'  or a.summary like '%"+keywords+"%'  or a.context like '%"+keywords+"%')"
					+ " UNION "
					+ "select m.id,m.title,'' summary,'' author,m.columnId,n.value0,3 type,m.createTime from helpinfo m,ConfValue n where m.columnId=n.id   and m.status='1' "
					+ "and (m.title like '%"+keywords+"%'  or m.keyword like '%"+keywords+"%'  or m.context like '%"+keywords+"%') order by createTime desc "
			        + " limit "+search.getPagestart()+","+search.getPagesize();
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			return sqlQuery.list();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "新闻，帮助联合模糊搜索查询！", e);
		}
		return null;
	}
	
	/**
	 * 新闻，帮助联合模糊搜索查询总行数
	 * @param keywords
	 * @return
	 */
	public int queryCountNH(String keywords) {
		int count=0;
		try {
			String sql = "select count(*) from ("
					+ "select a.id,a.title,a.summary,a.author,a.columnId,b.value0,1 type,a.createTime from News a,ConfValue b where a.columnId=b.id   and a.status='1' "
					+ "and (a.title like '%"+keywords+"%'  or a.keyword like '%"+keywords+"%'  or a.summary like '%"+keywords+"%'  or a.context like '%"+keywords+"%')"
					+ " UNION "
					+ "select m.id,m.title,'' summary,'' author,m.columnId,n.value0,3 type,m.createTime from helpinfo m,ConfValue n where m.columnId=n.id   and m.status='1' "
					+ "and (m.title like '%"+keywords+"%'  or m.keyword like '%"+keywords+"%'  or m.context like '%"+keywords+"%') order by createTime desc) z";
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			List list = sqlQuery.list();
			if(!(list == null || list.size() == 0)){
				//将bigInteger转换成Integer
				Object obj = list.get(0);
				count = Integer.parseInt(obj.toString());
			}
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "新闻，帮助联合模糊搜索查询总行数异常！", e);
		}
		return count;
	}
	
	/**
	 * 文章搜索查询,不加分页
	 * @param search
	 * @param keywords
	 * @return
	 */
	public List<Map> query(Search search, String keywords) {
		try {
			String sql = "select a.id,a.title,a.summary,a.author,a.columnId,b.value0,1 type,a.createTime from News a,ConfValue b where a.columnId=b.id   and a.status='1' "
					+ "and (a.title like '%"+keywords+"%'  or a.keyword like '%"+keywords+"%'  or a.summary like '%"+keywords+"%'  or a.context like '%"+keywords+"%')"
					+ " UNION "
					+ "select m.id,m.title,'' summary,'' author,m.columnId,n.value0,3 type,m.createTime from helpinfo m,ConfValue n where m.columnId=n.id   and m.status='1' "
					+ "and (m.title like '%"+keywords+"%'  or m.keyword like '%"+keywords+"%'  or m.context like '%"+keywords+"%') order by createTime desc ";
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			return sqlQuery.list();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		}
		return null;
	}
	
	/**
	 * 汉水商城web按类型搜索指定类型
	 * @param search
	 * @param keywords
	 * @param searchType：int ;1:mall,2:productx;3:news
	 * @return 30/12/2015
	 */
	public List<Map> queryByType(Search search, String keywords, int searchType){
		String sql = null;
		try {
			switch(searchType){
			case 1:
				sql = "select g.id,g.name title,g.brief summary,4 type,g.createTime createTime,g.logo imgUrl,'' costprice,'' currentprice from mall g where g.status='1' "
						+ "and ( g.name like '%"+keywords+"%' or g.brief like '%"+keywords+"%'  or g.introduce like '%"+keywords+"%')";
				break;
			case 2:
				sql = "select e.id,e.title,e.summary,2 type,e.createTime,e.imgUrl,e.shopprice costprice,e.price currentprice from productx e where e.status='0' "
				+ "and ( e.title like '%"+keywords+"%'  or e.keyword like '%"+keywords+"%'  or e.summary like '%"+keywords+"%'  or e.context like '%"+keywords+"%')";
				break;
			case 3:
				sql = "select a.id,a.title,a.summary,1 type,a.createTime,a.imgUrl,'' costprice,'' currentprice  from News a where a.status='1' "
				+ "and ( a.title like '%"+keywords+"%'  or a.keyword like '%"+keywords+"%'  or a.summary like '%"+keywords+"%'  or a.context like '%"+keywords+"%')";
				break;
			}
			sql += "order by createTime desc " + " limit "+search.getPagestart()+","+search.getPagesize();
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			return sqlQuery.list();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "查询指定类型信息的模糊搜索查询！", e);
		}
		return null;
	}
	
	/**
	 * 市局
	 * @param search
	 * @param keywords
	 * @return
	 */
	public List<Map> queryByPolice(Search search, String keywords) {
	    try {
	      String sql = "select a.id,a.title,a.falseCreateTime from News a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3')"
	          + "and ( a.title like '%"+keywords+"%'  or a.keyword like '%"+keywords+"%'  or a.summary like '%"+keywords+"%'  or a.context like '%"+keywords+"%')"
	          + " UNION "
	          + "select w.id,w.title,w.falseCreateTime from Works w where "
	          + "( w.title like '%"+keywords+"%'  or w.keyword like '%"+keywords+"%'  or w.summary like '%"+keywords+"%'  or w.context like '%"+keywords+"%')"
	          + " UNION "
	          + "select n.id,n.title,n.falseCreateTime from Notice n where "
	          + "( n.title like '%"+keywords+"%'  or n.keyword like '%"+keywords+"%'  or n.summary like '%"+keywords+"%'  or n.context like '%"+keywords+"%')"
	          + " UNION "
	          + "select f.id,f.title,f.falseCreateTime from Files f where "
	          + "( f.title like '%"+keywords+"%'  or f.keyword like '%"+keywords+"%'  or f.summary like '%"+keywords+"%'  or f.context like '%"+keywords+"%')"
	          + " UNION "
	          + "select fa.id,fa.title,fa.falseCreateTime from Faxes fa where "
	          + "( fa.title like '%"+keywords+"%'  or fa.keyword like '%"+keywords+"%'  or fa.summary like '%"+keywords+"%'  or fa.context like '%"+keywords+"%')"
	          + " UNION "
	          + "select c.id,c.title,c.falseCreateTime from Columns c where "
	          + "( c.title like '%"+keywords+"%'  or c.keyword like '%"+keywords+"%'  or c.summary like '%"+keywords+"%'  or c.context like '%"+keywords+"%')"
	          + " order by falseCreateTime desc "
	          + " limit "+search.getPagestart()+","+search.getPagesize();
	      SQLQuery sqlQuery = em().createSQLQuery(sql);
	      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
	      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	      return sqlQuery.list();
	    } catch (Exception e) {
	      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
	    }
	    return null;
	  }
	/**
	   * web police
	   * 搜索模糊查询
	   * @return
	   */
	  public List<Map> queryByPoliceN(Search search, String keywords) {
	    try {
	      String sql = "select a.id,a.title,a.falseCreateTime from News a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3')"
	          + "and ( a.title like concat('%',?,'%')  )"
	          + " order by falseCreateTime desc "
	          + " limit ?,?;";
	      SQLQuery sqlQuery = em().createSQLQuery(sql);
	      sqlQuery.setParameter(0,keywords);
	      sqlQuery.setParameter(1,search.getPagestart());
	      sqlQuery.setParameter(2, search.getPagesize());
	      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
	      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	      return sqlQuery.list();
	    } catch (Exception e) {
	      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
	    }
	    return null;
	  }
	  
	  
	  public List<Map> queryByPoliceXzgajNews(Search search, String keywords) {
		    try {
		      String sql = "select a.id,a.title,a.falseCreateTime from xzgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3')"
		          + "and ( a.title like concat('%',?,'%')   )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
	  public List<Map> queryByPoliceDjgajNews(Search search, String keywords) {
		    try {
		      String sql = "select a.id,a.title,a.falseCreateTime from djgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3')"
		          + "and ( a.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  public List<Map> queryByPoliceLhkgajNews(Search search, String keywords) {
		    try {
		      String sql = "select a.id,a.title,a.falseCreateTime from lhkgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3' or a.status='7')"
		          + "and ( a.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
	  public List<Map> queryByPoliceLhkgajWorks(Search search, String keywords) {
		    try {
		      String sql = "select a.id,a.title,a.falseCreateTime from lhkgajworks a where  (a.status='1' or a.status='3' or a.status='7')"
		          + "and ( a.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
	  public List<Map> queryByPoliceXdgajNews(Search search, String keywords) {
		    try {
		      String sql = "select a.id,a.title,a.falseCreateTime from xdgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3')"
		          + "and ( a.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
	  public List<Map> queryByPoliceW(Search search, String keywords) {
		    try {
		      String sql = "select w.id,w.title,w.falseCreateTime from Works w where "
		          + "( w.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
	  public List<Map> queryByPoliceNS(Search search, String keywords) {
		    try {
		      String sql = "select n.id,n.title,n.falseCreateTime from Notice n where "
		          + "( n.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
	  public List<Map> queryByPoliceF(Search search, String keywords) {
		    try {
		      String sql = "select f.id,f.title,f.falseCreateTime from Files f where "
		          + "( f.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  public List<Map> queryByPoliceFA(Search search, String keywords) {
		    try {
		      String sql = "select fa.id,fa.title,fa.falseCreateTime from Faxes fa where "
		          + "( fa.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  public List<Map> queryByPoliceC(Search search, String keywords) {
		    try {
		      String sql = "select c.id,c.title,c.falseCreateTime from Columns c where "
		          + "( c.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  /**
		 * web police搜索查询总行数
		 * @param keywords
		 * @return
		 */
		public String queryCountP(String keywords) {
			String count="0";
			try {
				String sql = "select count(*) as cu from ("
						  + "select a.id,a.title,a.falseCreateTime from News a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3') "
				          + "and ( a.title like concat('%',?,'%')    )"
				          + " UNION "
				          + "select w.id,w.title,w.falseCreateTime from Works w where "
				          + "( w.title like concat('%',?,'%')    )"
				          + " UNION "
				          + "select n.id,n.title,n.falseCreateTime from Notice n where "
				          + "( n.title like concat('%',?,'%')    )"
				          + " UNION "
				          + "select f.id,f.title,f.falseCreateTime from Files f where "
				          + "( f.title like concat('%',?,'%')    )"
				          + " UNION "
				          + "select fa.id,fa.title,fa.falseCreateTime from Faxes fa where "
				          + "( fa.title like concat('%',?,'%')  )"
				          + " UNION "
				          + "select c.id,c.title,c.falseCreateTime from Columns c where "
				          + "( c.title like concat('%',?,'%')    )"
				          + " order by falseCreateTime desc ) z"+";";
				SQLQuery sqlQuery = em().createSQLQuery(sql);
				sqlQuery.setParameter(0,keywords);
				sqlQuery.setParameter(1,keywords);
				sqlQuery.setParameter(2,keywords);
				sqlQuery.setParameter(3,keywords);
				sqlQuery.setParameter(4,keywords);
				sqlQuery.setParameter(5,keywords);
				sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List list = sqlQuery.list();
				count=list.get(0).toString();
				count=count.substring(4, count.length()-1);	
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "搜索查询总行数异常！", e);
			}
			return count;
		}
		
		
		
		/**襄州区公安局
		 * web police搜索查询总行数
		 * @param keywords
		 * @return
		*/
		public String queryCountXzgajP(String keywords) {
			String count="0";
			try {
				String sql = "select count(*) as cu from ("
						  + "select a.id,a.title,a.falseCreateTime from xzgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3') "
				          + "and ( a.title like concat('%',?,'%') )"
				          + " order by falseCreateTime desc ) z";
				SQLQuery sqlQuery = em().createSQLQuery(sql);
				sqlQuery.setParameter(0,keywords);
				sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List list = sqlQuery.list();
				count=list.get(0).toString();
				count=count.substring(4, count.length()-1);	
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "搜索查询总行数异常！", e);
			}
			return count;
		}
		

		/**东津公安分局
		 * web police搜索查询总行数
		 * @param keywords
		 * @return
		*/
		public String queryCountDjgajP(String keywords) {
			String count="0";
			try {
				String sql = "select count(*) as cd from ("
						  + "select a.id,a.title,a.falseCreateTime from djgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3') "
				          + "and ( a.title like concat('%',?,'%') )"
				          + " order by falseCreateTime desc ) z";
				SQLQuery sqlQuery = em().createSQLQuery(sql);
				sqlQuery.setParameter(0,keywords);
				sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List list = sqlQuery.list();
				count=list.get(0).toString();
				count=count.substring(4, count.length()-1);		
						
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "搜索查询总行数异常！", e);
			}
			return count;
		}
		/**老河口公安分局
		 * web police搜索查询总行数
		 * @param keywords
		 * @return
		*/
		public String queryCountLhkgajP(String keywords) {
			String count="0";
			try {
				String sql = "select count(*) as cd from ("
						  + "select a.id,a.title,a.falseCreateTime from lhkgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3' or a.status='7') "
				          + "and ( a.title like concat('%',?,'%') )"
				          + " UNION "
				          + "select a.id,a.title,a.falseCreateTime from lhkgajworks a where  (a.status='1' or a.status='3' or a.status='7') "
				          + "and ( a.title like concat('%',?,'%') )"
				          + " order by falseCreateTime desc ) z";
				SQLQuery sqlQuery = em().createSQLQuery(sql);
				sqlQuery.setParameter(0,keywords);
				sqlQuery.setParameter(1,keywords);
				sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List list = sqlQuery.list();
				count=list.get(0).toString();
				count=count.substring(4, count.length()-1);		
						
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "搜索查询总行数异常！", e);
			}
			return count;
		}
		/**地质大队
		 * web police搜索查询总行数
		 * @param keywords
		 * @return
		*/
		public String queryCountDzddP(String keywords) {
			String count="0";
			try {
				String sql = "select count(*) as cd from ("
						  + "select a.id,a.title,a.falseCreateTime from dzddnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3' or a.status='7') "
				          + "and ( a.title like concat('%',?,'%') )"
				          + " UNION "
				          + "select a.id,a.title,a.falseCreateTime from dzddworks a where  (a.status='1' or a.status='3' or a.status='7') "
				          + "and ( a.title like concat('%',?,'%') )"
				          + " order by falseCreateTime desc ) z";
				SQLQuery sqlQuery = em().createSQLQuery(sql);
				sqlQuery.setParameter(0,keywords);
				sqlQuery.setParameter(1,keywords);
				sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List list = sqlQuery.list();
				count=list.get(0).toString();
				count=count.substring(4, count.length()-1);		
						
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "搜索查询总行数异常！", e);
			}
			return count;
		}
		/**
		 * 襄东公安分局
		 * web police搜索查询总行数
		 * @param keywords
		 * @return
		*/
		public String queryCountXdgajP(String keywords) {
			String count="0";
			try {
				String sql = "select count(*) as cd from ("
						  + "select a.id,a.title,a.falseCreateTime from xdgajnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3') "
				          + "and ( a.title like concat('%',?,'%') )"
				          + " order by falseCreateTime desc ) z";
				SQLQuery sqlQuery = em().createSQLQuery(sql);
				sqlQuery.setParameter(0,keywords);
				sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List list = sqlQuery.list();
				count=list.get(0).toString();
				count=count.substring(4, count.length()-1);		
						
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "搜索查询总行数异常！", e);
			}
			return count;
		}
		public List<Map> queryByPoliceDzddNews(Search search, String keywords) {
		    try {
		      String sql = "select a.id,a.title,a.falseCreateTime from dzddnews a,ConfValue b where a.columnId=b.id   and (a.status='1' or a.status='3' or a.status='7')"
		          + "and ( a.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
	  public List<Map> queryByPoliceDzddWorks(Search search, String keywords) {
		    try {
		      String sql = "select a.id,a.title,a.falseCreateTime from dzddworks a where  (a.status='1' or a.status='3' or a.status='7')"
		          + "and ( a.title like concat('%',?,'%')  )"
		          + " order by falseCreateTime desc "
		          + " limit ?,?;";
		      SQLQuery sqlQuery = em().createSQLQuery(sql);
		      // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		      sqlQuery.setParameter(0,keywords);
		      sqlQuery.setParameter(1,search.getPagestart());
		      sqlQuery.setParameter(2, search.getPagesize());
		      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		      return sqlQuery.list();
		    } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
		    return null;
		  }
	  
}
